package com.housekeeping.housekeeping.serviceTest.dao.impl;/*
 * @author  pyh
 * @date  2021/1/22 0022 下午 3:28
 * @version 1.0
 * @since JDK1.8_241
 */

import com.housekeeping.housekeeping.advertisingmanagement.entity.AdMessages;
import com.housekeeping.housekeeping.order.entity.Order;
import com.housekeeping.housekeeping.serviceTest.dao.OrderTestDao;
import com.housekeeping.housekeeping.serviceTest.entity.Orders;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OrderTestDaoImpl implements OrderTestDao {
    //分页查询
    @Override
    public List<Orders> getPagesList(int currentPage, int pageSize) throws SQLException {
        ArrayList<Orders> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Orders orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, currentPage);
            pStmt.setInt(2, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new Orders();
                orders.setId(rs.getString("sid"));
                orders.setClientName(rs.getString("client_name"));
                orders.setClientPhone(rs.getString("client_phone"));
                orders.setOrderStatus(rs.getInt("order_state"));
                orders.setCreateTime(rs.getDate("create_time"));
                orders.setIsDelete(rs.getInt("is_delete"));

                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }
    //查询总条数
    @Override
    public int getTotalCount() throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        int totalCount = 0;
        try {
            String sql = "select count(*) from sale_order";
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            /*while (rs.next()) {
                totalCount = rs.getInt(1);
            }*/
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, stmt, conn);
        }
        return totalCount;
    }
    // 查询订单记录
    @Override
    public Orders getOrderContent(String id) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pStmt = null;
        conn = DBManager.getConnection();
        Orders orders = null;
        try {
            String sql = "select * from sale_order where sid = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, id);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new Orders();
                orders.setId(rs.getString("sid"));
                orders.setClientName(rs.getString("client_name"));
                orders.setClientPhone(rs.getString("client_phone"));
                orders.setOrderStatus(rs.getInt("order_state"));
                orders.setCreateTime(rs.getDate("create_time"));
                orders.setIsDelete(rs.getInt("is_delete"));
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }

        return orders;
    }

    @Override
    public List<Orders> getStatusList(int status, int page, int size) throws SQLException {
        ArrayList<Orders> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Orders orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order where order_state = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setInt(2, page);
            pStmt.setInt(3, size);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new Orders();
                orders.setId(rs.getString("sid"));
                orders.setClientName(rs.getString("client_name"));
                orders.setClientPhone(rs.getString("client_phone"));
                orders.setOrderStatus(rs.getInt("order_state"));
                orders.setCreateTime(rs.getDate("create_time"));
                orders.setIsDelete(rs.getInt("is_delete"));

                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }

    @Override
    public int getStatusCount(int status) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from sale_order where order_state = ?";
            conn = DBManager.getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            rs = pStmt.executeQuery();

            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
    }

    @Override
    public List<Orders> getPhonesList(String phone, int status, int page, int size) throws SQLException {
        ArrayList<Orders> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Orders orders = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order where order_state = ? and client_phone = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setString(2, phone);
            pStmt.setInt(3, page);
            pStmt.setInt(4, size);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                orders = new Orders();
                orders.setId(rs.getString("sid"));
                orders.setClientName(rs.getString("client_name"));
                orders.setClientPhone(rs.getString("client_phone"));
                orders.setOrderStatus(rs.getInt("order_state"));
                orders.setCreateTime(rs.getDate("create_time"));
                orders.setIsDelete(rs.getInt("is_delete"));

                list.add(orders);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }

    @Override
    public int getStatusAndPhoneCount(int status, String phone) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        int totalCount = 0;
        try {
            conn = DBManager.getConnection();
            String sql = "select count(*) from sale_order where order_state = ? and client_phone = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setString(2, phone);
            rs = pStmt.executeQuery();
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return totalCount;
    }
}
